﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;


namespace DAL
{
    public class TonKhoDAL
    {
         private SqlConnection cn = null;
         public TonKhoDAL()
        {
            string stringConnection = ConfigurationSettings.AppSettings["connectionstring"].ToString();
            cn = new SqlConnection(stringConnection);
            cn.Open();
        }

        public void INSERT(TonKho D)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_TONKHO_Insert";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;

            cm.Parameters.AddWithValue("@NgayCapNhat", D.NgayCapNhat);
            cm.Parameters.AddWithValue("@MaSanPham", D.MaSanPham);
            cm.Parameters.AddWithValue("@TonSoLuong", D.TongSoLuong);
            cm.Parameters.AddWithValue("@ConLai", D.ConLai);
            cm.Parameters.AddWithValue("@DaBan", D.DaBan);

            cm.ExecuteNonQuery();
        }

        public void UPDATE(TonKho D)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_TONKHO_Update";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;

            cm.Parameters.AddWithValue("@NgayCapNhat", D.NgayCapNhat);
            cm.Parameters.AddWithValue("@MaSanPham", D.MaSanPham);
            cm.Parameters.AddWithValue("@TonSoLuong", D.TongSoLuong);
            cm.Parameters.AddWithValue("@ConLai", D.ConLai);
            cm.Parameters.AddWithValue("@DaBan", D.DaBan);

            cm.ExecuteNonQuery();
        }

        public void DELETE(int iD)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_TONKHO_Delete";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@MaTonKho", iD);
            cm.ExecuteNonQuery();
        }

        public TonKhoCollection GET(string Where, int PageIndex, int PageSize, string OrderBy, string OrderDirection, out int TotalRecords)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_TONKHO_Get";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@Where", Where);
            cm.Parameters.AddWithValue("@PageIndex", PageIndex);
            cm.Parameters.AddWithValue("@PageSize", PageSize);
            cm.Parameters.AddWithValue("@OrderBy", OrderBy);
            cm.Parameters.AddWithValue("@OrderDirection", OrderDirection);
            SqlParameter param = new SqlParameter("@TotalRecords", DbType.Int32);
            param.Direction = ParameterDirection.Output;
            cm.Parameters.Add(param);
            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            TonKhoCollection TonKhoCollection = new TonKhoCollection();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                TonKho D = new TonKho();
                D.MaTonKho = int.Parse(ds.Tables[0].Rows[i]["MaTonKho"].ToString());
                D.NgayCapNhat = DateTime.Parse(ds.Tables[0].Rows[i]["NgayCapNhat"].ToString());
                D.MaSanPham = int.Parse(ds.Tables[0].Rows[i]["MaSanPham"].ToString());
                D.TongSoLuong = int.Parse(ds.Tables[0].Rows[i]["TongSoLuong"].ToString());
                D.ConLai = int.Parse(ds.Tables[0].Rows[i]["ConLai"].ToString());
                D.DaBan = int.Parse(ds.Tables[0].Rows[i]["DaBan"].ToString());
                TonKhoCollection.Add(D);
            }
            TotalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());
            return TonKhoCollection;
        }
    }
}
